﻿using System;
using System.Collections.Generic;

using System.Text;
using System.Data.SqlClient;
using System.Data;

using System.Configuration;
using System.Data.SqlTypes;

namespace Corsat.Library.DataManager
{
    public static partial class Main
    {

        #region Private Methods


        static void OpenConnection()
        {
            if (connection.State != ConnectionState.Open)
                connection.Open();
        }

        static void CloseConnection()
        {
            if (connection.State != ConnectionState.Closed)
                connection.Close();
        }
        static DataSet GetData(StoredProcedure StoredProcedureName, SqlParameter[] Parameters)
        {
            try
            {
                command.CommandText = StoredProcedureName.ToString();
                command.CommandType = CommandType.StoredProcedure;

                command.Parameters.Clear();

                if (Parameters != null)
                    command.Parameters.AddRange(Parameters);

                DataSet dsMaster = new DataSet();
                adapter.Fill(dsMaster);

                return dsMaster;
            }
            catch (Exception exc)
            {
                return null;
            }
        }

        static SqlParameter CreateParameter(string Name, object Value)
        {

            SqlParameter param = command.CreateParameter();
            param.ParameterName = Name;
            if (Value.GetType() == typeof(DateTime))
            {
                DateTime inputDate = (DateTime)Value;

                SqlDateTime date = new SqlDateTime(inputDate.Year, inputDate.Month, inputDate.Day);

                param.Value = date;
            }
            else

            param.Value = Value;

            
            return param;
        }

        /// <summary>
        /// Converts the contents of a given DataTable to a List of Items.
        /// </summary>
        /// <param name="ItemsDataTable"></param>
        /// <returns></returns>
        static List<Logic.Item> ConvertTableToItems(DataTable ItemsDataTable)
        {
            try
            {
                List<Logic.Item> Items = new List<Logic.Item>();
                DataRow drItem;

                for (int i = 0; i < ItemsDataTable.Rows.Count; i++)
                {
                    Logic.Item item = new Logic.Item();
                    drItem = ItemsDataTable.Rows[i];

                    item.Id = Convert.ToInt32(drItem["ItemId"]);
                    item.Value = Convert.ToString(drItem["ItemName"]);

                    if (drItem["Description"] != DBNull.Value && drItem["Description"] != null && !String.IsNullOrEmpty(drItem["Description"].ToString()))
                        item.Description = Convert.ToString(drItem["Description"]);

                    Item Category = new Item();
                    Category.Id = Convert.ToInt32(drItem["CategoryId"]);
                    Category.Value = Convert.ToString(drItem["CategoryName"]);
                    item.Category = Category;

                    if (drItem["ParentItemId"] != DBNull.Value && drItem["ParentItemId"] != null && !String.IsNullOrEmpty(drItem["ParentItemId"].ToString()))
                    {
                        Item ParentItem = new Item();
                        ParentItem.Id = Convert.ToInt32(drItem["ParentItemId"]);
                        ParentItem.Value = Convert.ToString(drItem["ParentItemName"]);
                        item.Parent = ParentItem;
                    }

                    Items.Add(item);
                }

                return Items;
            }
            catch (Exception exc)
            {
                return null;
            }
        }

        /// <summary>
        /// Converts the contents of a given DataTable to a List of Items.
        /// </summary>
        /// <param name="ItemsDataTable"></param>
        /// <returns></returns>
        static List<Item> ConvertTableToSmallItems(DataTable ItemsDataTable)
        {
            try
            {
                List<Item> Items = new List<Item>();
                DataRow drItem;

                for (int i = 0; i < ItemsDataTable.Rows.Count; i++)
                {
                    Item item = new Item();
                    drItem = ItemsDataTable.Rows[i];

                    item.Id = Convert.ToInt32(drItem["ItemId"]);
                    item.Value = Convert.ToString(drItem["ItemName"]);

                    Items.Add(item);
                }

                return Items;
            }
            catch (Exception exc)
            {
                return null;
            }
        }

        #endregion

        #region Public Methods

        public static List<Item> GetAllItemsByCategory(Category Category)
        {
            try
            {
                SqlParameter paramCategory = new SqlParameter("CategoryId", Convert.ToInt32(Category));
                dtMaster = GetData(StoredProcedure.sp_GetAllItemsByCategory, new SqlParameter[] { paramCategory }).Tables[0];

                List<Item> Items = ConvertTableToSmallItems(dtMaster);

                return Items;
            }
            catch (Exception exc)
            {
                return null;
            }
        }

        public static List<Item> GetAllItemsByCategoryAndParentItem(Category Category, int parentItemId)
        {
            try
            {
                SqlParameter paramCategory = new SqlParameter("CategoryId", Convert.ToInt32(Category));
                SqlParameter paramParentItem = new SqlParameter("ParentItemId", Convert.ToInt32(parentItemId));
                dtMaster = GetData(StoredProcedure.sp_GetAllItemsByCategoryAndParentItem, new SqlParameter[] { paramCategory, paramParentItem }).Tables[0];

                List<Item> Items = ConvertTableToSmallItems(dtMaster);

                return Items;
            }
            catch (Exception exc)
            {
                return null;
            }
        }

        public static List<Logic.Item> GetAllRelatedItems(int CategoryId, int ParentItemId)
        {
            try
            {
                command.CommandText = StoredProcedure.SP_GetRelatedItems.ToString();
                command.CommandType = CommandType.StoredProcedure;

                command.Parameters.Clear();

                command.Parameters.Add(CreateParameter("CategoryId", CategoryId));
                command.Parameters.Add(CreateParameter("ParentItemId", ParentItemId));

                dtMaster = new DataTable();

                adapter.Fill(dtMaster);

                List<Logic.Item> Items = ConvertTableToItems(dtMaster);

                return Items;
            }
            catch (Exception exc)
            {
                return null;
            }
        }

        public static List<SmallAccount> GetAllAccounts()
        {
            try
            {
                //DataSet dsAccounts = GetData(StoredProcedure.sp_GetAllAccountsNames, new SqlParameter[] { new SqlParameter("@Year", DateTime.Now.Year) });
                DataSet dsAccounts = GetData(StoredProcedure.sp_GetAllAccountsWithCorsatId, new SqlParameter[] { new SqlParameter("@Year", DateTime.Now.Year) });
                List<SmallAccount> Accounts = new List<SmallAccount>();

                if (dsAccounts.Tables.Count > 0 && dsAccounts.Tables[0].Rows.Count > 0)
                {

                    for (int i = 0; i < dsAccounts.Tables[0].Rows.Count; i++)
                    {
                        DataRow drAccount = dsAccounts.Tables[0].Rows[i];

                        SmallAccount account = new SmallAccount();

                        if (drAccount["CorsatId"] != DBNull.Value)
                            account.CorsatId = Convert.ToInt32(drAccount["CorsatId"]);

                        if (drAccount["AccountId"] != null)
                        {
                            Item AccountName = new Item();
                            AccountName.Id = Convert.ToInt32(drAccount["AccountId"]);
                            AccountName.Value = drAccount["ArabicName"].ToString();

                            account.Account = AccountName;
                        }

                        Accounts.Add(account);
                    }

                }

                return Accounts;
            }
            catch (Exception exc)
            {
                return null;
            }
        }

        #endregion
    }
}
